package com.source.baseData.mapper;

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.source.baseData.entity.ToMarket;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.source.baseData.vo.ToMarketVO;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 * Mapper 接口
 * </p>
 *
 * @author Feng
 * @since 2021-03-01
 */

public interface ToMarketMapper extends BaseMapper<ToMarket> {

    @Select("SELECT city FROM to_market WHERE status=#{status} AND province=#{province}")
    List<String> getCities(@Param("status") Integer status, @Param("province") String province);

    @Select("SELECT district FROM to_market WHERE status=#{status} AND province=#{province} AND city=#{city}")
    List<String> getDistricts(@Param("status") Integer status, @Param("province") String province, @Param("city") String city);

    @Select("SELECT t.*,u.`name` userName FROM to_market t,sys_user u WHERE t.userId=u.userId AND t.isDelete=0 AND t.`status`=#{status}")
    List<ToMarketVO> findAllVo(@Param("status") Integer status);

    @Select("SELECT t.*,u.`name` userName FROM to_market t,sys_user u WHERE t.userId=u.userId AND t.isDelete=0 AND t.`status`=#{status}")
    Page<ToMarketVO> findAllVoPage(@Param("status") Integer status, Page<ToMarketVO> page);

    @Select("SELECT t.*,u.`name` userName FROM to_market t,sys_user u WHERE t.userId=u.userId AND t.isDelete=0 AND t.`status`=#{status} AND province=#{province} AND city=#{city} AND district=#{district}")
    List<ToMarketVO> findVoByDistrict(@Param("status") Integer status, @Param("province") String province, @Param("city") String city, @Param("district") String district);

    @Select("SELECT t.*,u.`name` userName FROM to_market t,sys_user u WHERE t.userId=u.userId AND t.isDelete=0 AND t.`status`=#{status} AND province=#{province} AND city=#{city} AND district=#{district}")
    Page<ToMarketVO> findVoByDistrictPage(@Param("status") Integer status, @Param("province") String province, @Param("city") String city, @Param("district") String district, Page<ToMarketVO> page);

    @Select("SELECT t.*,u.`name` userName FROM to_market t,sys_user u WHERE t.userId=u.userId AND t.isDelete=0 AND t.`status`=#{status} AND slaughterHouseName=#{name}")
    List<ToMarketVO> findVoBySlaughterHouseName(@Param("status") Integer status, @Param("province") String name);

    @Select("SELECT t.*,u.`name` userName FROM to_market t,sys_user u WHERE t.userId=u.userId AND t.isDelete=0 AND t.`status`=#{status} AND slaughterHouseName=#{name}")
    Page<ToMarketVO> findVoBySlaughterHouseNamePage(@Param("status") Integer status, @Param("province") String name, Page<ToMarketVO> page);

    @Select("SELECT t.*,u.`name` userName FROM to_market t,sys_user u WHERE t.userId=u.userId AND t.isDelete=0 AND t.`status`=#{status} AND stallName=#{name}")
    List<ToMarketVO> findByStallName(@Param("status") Integer status, @Param("province") String name);

    @Select("SELECT t.*,u.`name` userName FROM to_market t,sys_user u WHERE t.userId=u.userId AND t.isDelete=0 AND t.`status`=#{status} AND stallName=#{name}")
    Page<ToMarketVO> findByStallNamePage(@Param("status") Integer status, @Param("province") String name, Page<ToMarketVO> page);

    /*正常去向市场模糊查询：去向市场编号	去向市场名称	去向市场所在区县	去向市场地址	去向市场档口名称	负责人*/
    @Select("SELECT t.*,u.`name` userName \n" +
            "FROM to_market t,sys_user u \n" +
            "WHERE t.userId=u.userId \n" +
            "AND t.isDelete=0 \n" +
            "AND CONCAT(IFNULL(slaughterHouseId,''),'\\t',IFNULL(slaughterHouseName,''),'\\t',IFNULL(district,''),'\\t',IFNULL(addr,''),'\\t',IFNULL(stallName,''),'\\t',IFNULL(u.`name`,'')) LIKE CONCAT('%',#{word},'%')")
    List<ToMarketVO> like(@Param("word") String word);

    @Select("SELECT t.*,u.`name` userName \n" +
            "FROM to_market t,sys_user u \n" +
            "WHERE t.userId=u.userId \n" +
            "AND t.isDelete=0 \n" +
            "AND CONCAT(IFNULL(slaughterHouseId,''),'\\t',IFNULL(slaughterHouseName,''),'\\t',IFNULL(district,''),'\\t',IFNULL(addr,''),'\\t',IFNULL(stallName,''),'\\t',IFNULL(u.`name`,'')) LIKE CONCAT('%',#{word},'%')")
    Page<ToMarketVO> likePage(@Param("word") String word, Page<ToMarketVO> page);

    /*待审核去向市场模糊查询：去向市场编号	 去向市场名称  去向市场所在区县	去向市场地址	去向市场档口名称	负责人   状态*/
    @Select("SELECT t.*,u.`name` userName \n" +
            "FROM to_market t,sys_user u \n" +
            "WHERE t.userId=u.userId \n" +
            "AND t.isDelete=0 AND ( t.status = 2 or t.status = 3)  \n" +
            "AND CONCAT_WS(',',slaughterHouseId, slaughterHouseName, district, addr , stallName, u.`name` )   LIKE CONCAT('%',#{word},'%')  " +
            "  ")
    List<ToMarketVO> toBeReviewedLikeList(@Param("word") String word);

    @Select("SELECT t.*,u.`name` userName \n" +
            "FROM to_market t,sys_user u \n" +
            "WHERE t.userId=u.userId \n" +
            "AND t.isDelete=0 AND ( t.status = 2 or t.status = 3) \n" +
            //"AND CONCAT(IFNULL(slaughterHouseId,''),'\\t',IFNULL(slaughterHouseName,''),'\\t',IFNULL(district,''),'\\t',IFNULL(addr,''),'\\t',IFNULL(stallName,''),'\\t',IFNULL(u.`name`,''),'\\t',(CASE t.status WHEN 2 THEN '发布待审核' WHEN 3 THEN '删除待审核' ELSE '' END)) LIKE CONCAT('%',#{word},'%')")
            "AND CONCAT_WS(',',slaughterHouseId, slaughterHouseName, district, addr , stallName, u.`name` )   LIKE CONCAT('%',#{word},'%')  ")
    Page<ToMarketVO> toBeReviewedLikePage(@Param("word") String word, Page<ToMarketVO> page);

    @Select("SELECT COUNT(stallsId) FROM stalls WHERE isDelete=0 AND slaughterHouseId=#{id} AND YEAR(createDate)=#{year} AND MONTH(createDate)=#{month} AND status=1")
    int getStallNumByMonth(@Param("id") String slaughterHouseId, @Param("year") int year, @Param("month") int month);

    @Select("SELECT t.*,u.`name` userName FROM to_market t,sys_user u WHERE t.userId=u.userId AND t.isDelete=0 AND t.`status`=1 AND district = #{name}")
    List<ToMarketVO> findMarketByDistrictId(String name);

    @Select("SELECT t.*,u.`name` userName FROM to_market t,sys_user u WHERE t.userId=u.userId AND t.isDelete=0 AND t.`status` IN (2,3)")
    List<ToMarketVO> toBeReviewedList();

    @Select("SELECT t.*,u.`name` userName FROM to_market t,sys_user u WHERE t.userId=u.userId AND t.isDelete=0 AND t.`status` IN (2,3)")
    Page<ToMarketVO> toBeReviewedPage(Page<ToMarketVO> page);

    @Select("SELECT t.*,u.`name` userName FROM to_market t,sys_user u WHERE t.userId=u.userId AND t.isDelete=0 AND t.slaughterHouseId = #{id} AND u.isDelete = 0")
    ToMarketVO findById(String id);
}
